﻿Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient

Public Class EmpleadosDB
    Public Shared Function GetEmpleados() As DataSet
        Dim cmd As New SqlCommand("spGetEmpleados", DB.Conexion)
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet

        cmd.CommandType = CommandType.StoredProcedure
        da.SelectCommand = cmd
        da.Fill(ds, "Empleados")
        Return ds
    End Function
    Public Shared Function GetEmpleadosByDepartamento(ByVal id_departamento As Integer) As DataSet
        Dim cmd As New SqlCommand("spGetEmpleadosByDepartamento", DB.Conexion)
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet


        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@id_departamento", id_departamento)

        da.SelectCommand = cmd
        da.Fill(ds, "Empleados")
        Return ds
    End Function
    Public Shared Function EmpleadosNoJefesDepartamento() As DataSet
        Dim cmd As New SqlCommand("spGetEmpleadosNoJefesDepartamentos", DB.Conexion)
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet


        cmd.CommandType = CommandType.StoredProcedure

        da.SelectCommand = cmd
        da.Fill(ds, "Empleados")
        Return ds
    End Function

    Public Shared Function GetEmpleadosExceptoId(ByVal id_empleado As Integer) As DataSet
        Dim cmd As New SqlCommand("spGetEmpleadosExceptoId", DB.Conexion)
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet

        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@id_empleado", id_empleado)

        cmd.CommandType = CommandType.StoredProcedure

        da.SelectCommand = cmd
        da.Fill(ds, "Empleados")
        Return ds
    End Function

    Public Shared Sub InsertarEmpleado(ByVal id_departamento As Integer, ByVal id_jefe As Integer, ByVal salario As Double, ByVal fecha_ingreso As Date, ByVal id_tipo_documento As Integer, ByVal nro_documento As Integer, ByVal id_cargo As Integer, ByVal id_sucursal As Integer, ByVal nombre As String, ByVal apellido As String, ByVal fecha_nacimiento As Date, ByVal email As String)
        Dim cmd As New SqlCommand("spInsertEmpleado", DB.Conexion)
        cmd.CommandType = CommandType.StoredProcedure

        If id_departamento = 0 Then
            cmd.Parameters.AddWithValue("@id_departamento", DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@id_departamento", id_departamento)
        End If

        If id_jefe = 0 Then
            cmd.Parameters.AddWithValue("@id_jefe", DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@id_jefe", id_jefe)
        End If


        cmd.Parameters.AddWithValue("@salario", salario)
        cmd.Parameters.AddWithValue("@fecha_ingreso", fecha_ingreso)
        cmd.Parameters.AddWithValue("@id_tipo_documento", id_tipo_documento)
        cmd.Parameters.AddWithValue("@nro_documento", nro_documento)
        cmd.Parameters.AddWithValue("@id_cargo", id_cargo)
        If id_sucursal = 0 Then
            cmd.Parameters.AddWithValue("@id_sucursal", DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@id_sucursal", id_sucursal)
        End If

        cmd.Parameters.AddWithValue("@nombre", nombre)
        cmd.Parameters.AddWithValue("@apellido", apellido)
        cmd.Parameters.AddWithValue("@fecha_nacimiento", fecha_nacimiento)
        If email = "" Then
            cmd.Parameters.AddWithValue("@email", DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@email", email)
        End If

        cmd.Parameters.Add("@Error", SqlDbType.Int).Direction = ParameterDirection.Output
        cmd.Parameters.Add("@ErrorDesc", SqlDbType.NVarChar, 4000).Direction = ParameterDirection.Output
        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()


        If cmd.Parameters("@error").Value <> 0 Then
            Throw New ApplicationException(cmd.Parameters("@ErrorDesc").Value.ToString())
        End If


    End Sub

    Public Shared Sub ActualizarEmpleado(ByVal id_empleado As Integer, ByVal id_departamento As Integer, ByVal id_jefe As Integer, ByVal salario As Double, ByVal fecha_ingreso As Date, ByVal id_tipo_documento As Integer, ByVal nro_documento As Integer, ByVal id_cargo As Integer, ByVal id_sucursal As Integer, ByVal nombre As String, ByVal apellido As String, ByVal fecha_nacimiento As Date, ByVal email As String)
        Dim cmd As New SqlCommand("spUpdateEmpleado", DB.Conexion)
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.AddWithValue("@id_empleado", id_empleado)

        If id_departamento = 0 Then
            cmd.Parameters.AddWithValue("@id_departamento", DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@id_departamento", id_departamento)
        End If

        If id_jefe = 0 Then
            cmd.Parameters.AddWithValue("@id_jefe", DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@id_jefe", id_jefe)
        End If


        cmd.Parameters.AddWithValue("@salario", salario)
        cmd.Parameters.AddWithValue("@fecha_ingreso", fecha_ingreso)
        cmd.Parameters.AddWithValue("@id_tipo_documento", id_tipo_documento)
        cmd.Parameters.AddWithValue("@nro_documento", nro_documento)
        cmd.Parameters.AddWithValue("@id_cargo", id_cargo)
        If id_sucursal = 0 Then
            cmd.Parameters.AddWithValue("@id_sucursal", DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@id_sucursal", id_sucursal)
        End If

        cmd.Parameters.AddWithValue("@nombre", nombre)
        cmd.Parameters.AddWithValue("@apellido", apellido)
        cmd.Parameters.AddWithValue("@fecha_nacimiento", fecha_nacimiento)
        If email = "" Then
            cmd.Parameters.AddWithValue("@email", DBNull.Value)
        Else
            cmd.Parameters.AddWithValue("@email", email)
        End If

        cmd.Parameters.Add("@Error", SqlDbType.Int).Direction = ParameterDirection.Output
        cmd.Parameters.Add("@ErrorDesc", SqlDbType.NVarChar, 4000).Direction = ParameterDirection.Output
        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()


        If cmd.Parameters("@error").Value <> 0 Then
            Throw New ApplicationException(cmd.Parameters("@ErrorDesc").Value.ToString())
        End If

    End Sub

    Public Shared Sub EliminarEmpleado(ByVal id_empleado As Integer)
        Dim cmd As New SqlCommand("spDeleteEmpleado", DB.Conexion)

        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@id_empleado", id_empleado)
        cmd.Parameters.Add("@error", SqlDbType.Int)
        cmd.Parameters.Add("@ErrorDesc", SqlDbType.NVarChar, 4000)
        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

        If cmd.Parameters("@error").Value <> 0 Then
            Throw New ApplicationException(cmd.Parameters("@ErrorDesc").Value.ToString())
        End If
    End Sub


End Class
